package com.whzykj.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.whzykj.model.OrderDetails;
import com.whzykj.model.OrderInfo;
import com.whzykj.service.OrderService;

public class OrderServiceImpl implements OrderService {
	@Override
	public List getOderInfoList(String orderDate, Integer stationId,String status,String orderId,String custName,String mobile,int start,int rows) {
		List list=new ArrayList();
		List<OrderInfo> orderList=OrderInfo.me.find(getOrderinfoSQL(orderDate, stationId,status,orderId, custName, mobile,start,rows));
		List<OrderDetails> detailsList=OrderDetails.me.find(getOrderDetailsSQL(orderDate, stationId,status,orderId, custName, mobile));
		for(OrderInfo order:orderList){
			List row=new ArrayList();
			row.add(order.getStr("ORDER_ID"));
			String str=String.valueOf(order.get("ORDER_DATE"));
			row.add(str.substring(0,19));
			row.add(order.getBigDecimal("TOTAL_AMOUNTS"));
			row.add(order.getBigDecimal("MINUS_AMOUNTS"));
			row.add(order.getBigDecimal("PAY_AMOUNTS"));
			row.add(order.getStr("DICT_NAME"));
			List res=findDetailsData(order.getStr("ORDER_ID"),detailsList);
			row.add(Integer.valueOf(String.valueOf(res.get(0))));
			row.add((List)res.get(1));		
			row.add(order.getStr("CUST_NAME"));
			String position=order.getStr("POSITION");
			row.add(order.getStr("STATION_NAME")+"[档口号："+(position==null?"":position)+"]");
			row.add(order.getStr("MOBILE"));
			row.add(String.valueOf(order.get("TAKE_DATE")));
			list.add(row);
			row=null;
		}
		System.out.println(list);
		return list;
	}
		
	private List findDetailsData(String orderId,List<OrderDetails> list){
		List data=new ArrayList();
		List row=null;
		int cnt=0;
		for(OrderDetails od:list){
			if(od.getStr("ORDER_ID").equals(orderId)){
				row=new ArrayList();
				row.add(od.getStr("GOODS_NAME"));
				row.add(od.getBigDecimal("ORIGINAL_PRICE"));
				row.add(od.getBigDecimal("SALE_PRICE"));
				cnt=cnt+od.getInt("SALE_NUM");
				row.add(od.getInt("SALE_NUM"));
				data.add(row);
				row=null;
			}
		}
		List res=new ArrayList();
		res.add(cnt);
		res.add(data);
		return res;
	}

	private String getWhereCondition(String orderDate, Integer stationId,String status,String orderId,String custName,String mobile){
		StringBuffer where=new StringBuffer(" where 1=1 ");
		if(orderDate==null || orderDate.equals("")){
			where.append(" and TO_DAYS(oi.ORDER_DATE)=TO_DAYS(NOW())");
		}else{
			where.append(" and TO_DAYS(oi.ORDER_DATE)=TO_DAYS('"+orderDate+"')");
		}
		if(stationId!=null && stationId!=-1){
			where.append(" and sta.STATION_ID="+stationId);
		}
		if(status!=null && status.trim().length()>0 && !status.equals("-1")){
			where.append(" and oi.STATUS ='"+status+"'");
		}
		if(orderId!=null && orderId.trim().length()>0){
			where.append(" and oi.ORDER_ID like '%"+orderId+"%'");
		}
		if(custName!=null && custName.trim().length()>0){
			where.append(" and cust.CUST_NAME like '%"+custName+"%'");
		}
		if(mobile!=null && mobile.trim().length()>0){
			where.append(" and cust.MOBILE LIKE '%"+mobile+"%'");
		}
		return where.toString();
	}
	private String getOrderinfoSQL(String orderDate, Integer stationId,String status,String orderId,String custName,String mobile,int start,int rows){
		StringBuffer sql=new StringBuffer("select oi.*,dic.DICT_NAME,cust.CUST_NAME,sta.STATION_NAME,cust.MOBILE,cust.POSITION FROM orderinfo oi  ");
		sql.append(" INNER JOIN dictionary dic on oi.`STATUS`=dic.DICT_CODE  ");
		sql.append(" INNER JOIN customer cust on oi.CUST_ID=cust.CUST_ID ");
		sql.append(" INNER JOIN station sta on cust.TAKE_STATION_ID=sta.STATION_ID");
		sql.append(getWhereCondition(orderDate, stationId,status,orderId, custName, mobile));		
		sql.append(" ORDER BY oi.ORDER_DATE DESC ");
		sql.append(" LIMIT "+start+","+rows);
		return sql.toString();
		
	}
	
	private String getOrderDetailsSQL(String orderDate, Integer stationId,String status,String orderId,String custName,String mobile){	
		StringBuffer sql=new StringBuffer(" select oi.ORDER_ID,od.ORIGINAL_PRICE,od.SALE_PRICE,od.SALE_NUM,gi.GOODS_NAME from orderinfo oi ");
		sql.append(" inner join orderdetails od on oi.ORDER_ID = od.ORDER_ID ");
		sql.append(" INNER JOIN customer cust on oi.CUST_ID=cust.CUST_ID ");
		sql.append(" INNER JOIN goodsinfo gi on od.GOODS_ID=gi.GOODS_ID ");
		sql.append(" INNER JOIN station sta on cust.TAKE_STATION_ID=sta.STATION_ID ");
		sql.append(getWhereCondition(orderDate, stationId,status,orderId, custName, mobile));		
		return sql.toString(); 
	}

	@Override
	public Integer getOderInfoListCount(String orderDate, Integer stationId,
			String status, String orderId, String custName, String mobile) {
		StringBuffer sql=new StringBuffer("select count(oi.ORDER_DATE) FROM orderinfo oi ");
		sql.append(" INNER JOIN dictionary dic on oi.`STATUS`=dic.DICT_CODE  ");
		sql.append(" INNER JOIN customer cust on oi.CUST_ID=cust.CUST_ID ");
		sql.append(" INNER JOIN station sta on cust.TAKE_STATION_ID=sta.STATION_ID");
		sql.append(getWhereCondition(orderDate, stationId,status,orderId, custName, mobile));
		int cnt=Db.queryLong(sql.toString()).intValue();
		return cnt;
	}
	
	/**
	 * 
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @return
	 */
	public List<Record> getAssignList(String startDate,String endDate,Integer stationid,String status){
		StringBuffer sb=new StringBuffer("select oi.ORDER_ID,cust.CUST_NAME,cust.MOBILE,oi.TAKE_DATE,sta.STATION_NAME,ui.USERNAME,ui.MOBILE as MOBILE1,");
		sb.append(" cust.POSITION,gi.GOODS_NAME,od.SALE_NUM,dict.DICT_NAME,oi.ORDER_DATE,od.SALE_PRICE,od.SALE_PRICE*od.SALE_NUM as AMMOUNTS");
		sb.append(" FROM orderinfo oi");
		sb.append(" inner join orderdetails od on oi.ORDER_ID=od.ORDER_ID");
		sb.append(" inner join customer cust on cust.CUST_ID=oi.CUST_ID");
		sb.append(" inner join station sta on sta.STATION_ID=cust.TAKE_STATION_ID");
		sb.append(" inner JOIN goodsinfo gi on gi.GOODS_ID=od.GOODS_ID");
		sb.append(" inner JOIN dictionary dict on dict.DICT_CODE=oi.STATUS");
		sb.append(" INNER JOIN userinfo ui on ui.userid=sta.STATION_MASTER");
		sb.append(getAssignWhereSQL(startDate,endDate,stationid,status));
		sb.append(" ORDER BY sta.STATION_ID,cust.CUST_NAME,oi.ORDER_ID");
		List<Record> list=null;
		try {
			list = Db.find(sb.toString());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		list=dealRecordList(list);
		return list;
	}
	
	/**
	 * 处理列表中的数据
	 * @param list
	 * @return
	 */
	private List<Record> dealRecordList(List<Record> list){
		if(list==null || list.size()==0){
			return list;
		}
		List<Record> rList=new ArrayList<Record>();
		Record r1=list.get(0);
		int total=r1.getInt("SALE_NUM");
		BigDecimal ammounts=r1.getBigDecimal("ammounts");
		int totals=total;
		BigDecimal totalsAmmounts=ammounts;
		rList.add(r1);
		for(int i=1,cnt=list.size();i<cnt;i++){
			Record r2=list.get(i);
			totals=totals+r2.getInt("sale_num");
			totalsAmmounts=totalsAmmounts.add(r2.getBigDecimal("ammounts"));
			if(!r1.getStr("order_id").equals(r2.getStr("order_id"))){
				rList.add(new Record().set("sale_num", total).set("cust_name", "合计").set("ammounts", ammounts));
				rList.add(r2);
				total=r2.getInt("SALE_NUM");
				ammounts=r2.getBigDecimal("ammounts");
			}else{
				total=total+r2.getInt("SALE_NUM");
				ammounts=ammounts.add(r2.getBigDecimal("ammounts"));
				Record temp=new Record();
				temp.set("goods_name", r2.getStr("goods_name"));
				temp.set("sale_num", r2.getInt("sale_num"));
				temp.set("dict_name", r2.get("dict_name"));
				temp.set("order_date", r2.get("order_date"));
				temp.set("sale_price", r2.getBigDecimal("sale_price"));
				temp.set("ammounts", r2.getBigDecimal("ammounts"));
				// add
				temp.set("ORDER_ID", r2.getStr("ORDER_ID"));
				temp.set("CUST_NAME", r2.getStr("CUST_NAME"));
				temp.set("MOBILE", r2.getStr("MOBILE"));
				temp.set("TAKE_DATE", r2.get("TAKE_DATE"));
				temp.set("STATION_NAME", r2.getStr("STATION_NAME"));
				temp.set("USERNAME", r2.getStr("USERNAME"));
				temp.set("MOBILE1", r2.getStr("MOBILE1"));
				temp.set("POSITION", r2.getStr("POSITION"));
				rList.add(temp);
			}
			r1=list.get(i);
		}
		rList.add(new Record().set("sale_num", total).set("cust_name", "合计").set("ammounts", ammounts));
		rList.add(new Record().set("cust_name", "汇总统计").set("sale_num", totals).set("ammounts", totalsAmmounts));
		return rList;
	}
	
	/**
	 * 根据查询条件获取财务订单报表
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @param status
	 * @return
	 */
	public List<Record> getFinanceOrderList(String startDate,String endDate,Integer stationid,String status){
		List<Record> recList=new ArrayList<Record>();
		// 1 ：获取站点信息列表
		List<Record> staList=Db.find("select sta.STATION_ID,sta.STATION_NAME,sta.STATION_ADDRESS,ui.USERNAME,ui.MOBILE,sta.SEND_TIME from station sta INNER JOIN userinfo ui on ui.USERID=sta.STATION_MASTER");
		// 2：获取特定查询条件下的所有订单统计列表信息
		StringBuffer sb=new StringBuffer(" select sta.STATION_ID,gi.GOODS_NAME,od.SALE_PRICE,SUM(od.SALE_NUM) as SALE_NUMS");
		sb.append(" from orderinfo oi INNER JOIN orderdetails od on oi.ORDER_ID=od.ORDER_ID");
		sb.append(" INNER JOIN customer cust on cust.CUST_ID=oi.CUST_ID");
		sb.append(" INNER JOIN station sta on sta.STATION_ID=cust.TAKE_STATION_ID");
		sb.append(" INNER JOIN goodsinfo gi on gi.GOODS_ID=od.GOODS_ID");
		sb.append(" INNER JOIN dictionary dict on dict.DICT_CODE=oi.`STATUS`");
		sb.append(getAssignWhereSQL(startDate,endDate,stationid,status));
		sb.append(" GROUP BY sta.STATION_ID,gi.GOODS_NAME,od.SALE_PRICE");
		List<Record> orderList=Db.find(sb.toString());
		String dateStr=startDate+"<br/>-<br/>"+endDate;
		if(startDate.equals(endDate)){
			dateStr=startDate;
		}
		Record record=null;
		for(Record rec:orderList){
			record=new Record();
			record.set("station_id", rec.getInt("station_id"));
			record.set("order_date", dateStr);
			record.set("station", getStationStr(staList,rec.getInt("station_id")));
			record.set("send_time", getSendTimeById(staList,rec.getInt("station_id")));
			record.set("goods_name", rec.getStr("goods_name"));
			record.set("assign_num", rec.getBigDecimal("sale_nums"));
			record.set("sale_price", rec.getBigDecimal("sale_price"));
			record.set("ammounts", rec.getBigDecimal("sale_price").multiply(rec.getBigDecimal("sale_nums")));
			record.set("sale_num", rec.getBigDecimal("sale_nums"));
			recList.add(record);
			record=null;
		}
		recList=dealFinanceRecordList(recList);
		return recList;
	}
	
	/**
	 * 获取需到货时间
	 * @param staList
	 * @param stationid
	 * @return
	 */
	private String getSendTimeById(List<Record> staList,int stationid){
		String sendTime="";
		if(staList!=null && staList.size()>0){
			for(Record rec:staList){
				if(rec.getInt("station_id")==stationid){
					sendTime=rec.getStr("send_time");
					break;
				}
			}
		}
		return sendTime;
	}
	
	
	/* 处理列表中的数据
	 * @param list
	 * @return
	 */
	private List<Record> dealFinanceRecordList(List<Record> list){
		if(list==null || list.size()==0){
			return list;
		}
		List<Record> rList=new ArrayList<Record>();
		Record r1=list.get(0);
		BigDecimal assign_num=r1.getBigDecimal("assign_num");
		BigDecimal sale_num=r1.getBigDecimal("sale_num");
		BigDecimal ammounts=r1.getBigDecimal("ammounts");
		BigDecimal assign_num_total=assign_num;
		BigDecimal sale_num_total=sale_num;
		BigDecimal totalsAmmounts=ammounts;
		rList.add(r1);
		for(int i=1,cnt=list.size();i<cnt;i++){
			Record r2=list.get(i);
//			totals=totals+r2.getInt("sale_num");
			sale_num_total=sale_num_total.add(r2.getBigDecimal("sale_num"));
			assign_num_total=assign_num_total.add(r2.getBigDecimal("assign_num"));
			totalsAmmounts=totalsAmmounts.add(r2.getBigDecimal("ammounts"));
			if(r1.getInt("station_id").intValue()!=r2.getInt("station_id").intValue()){
				rList.add(new Record().set("sale_num", sale_num).set("goods_name", "合计").set("ammounts", ammounts).set("assign_num", assign_num));
				rList.add(r2);
				sale_num=r2.getBigDecimal("sale_num");
				assign_num=r2.getBigDecimal("assign_num");
				ammounts=r2.getBigDecimal("ammounts");
			}else{
				sale_num=sale_num.add(r2.getBigDecimal("sale_num"));
				assign_num=assign_num.add(r2.getBigDecimal("assign_num"));
				ammounts=ammounts.add(r2.getBigDecimal("ammounts"));
				rList.add(r2);
			}
			r1=list.get(i);
		}
		rList.add(new Record().set("sale_num", sale_num).set("goods_name", "合计").set("ammounts", ammounts).set("assign_num", assign_num));
		rList.add(new Record().set("goods_name", "汇总统计").set("sale_num", sale_num_total).set("ammounts", totalsAmmounts).set("assign_num", assign_num_total));
		return rList;
	}
	
	private String getStationStr(List<Record> staList,int stationid){
		StringBuffer str=new StringBuffer("");
		if(staList!=null && staList.size()>0){
			for(Record rec:staList){
				if(rec.getInt("station_id").intValue()==stationid){
					str.append(rec.getStr("station_name")+"<br/>");
					str.append("("+rec.getStr("station_address")+")");
					str.append(rec.getStr("username")+"<br/>");
					str.append(rec.getStr("mobile"));
					break;
				}
			}
		}
		return str.toString();
	}
	
	/**
	 * 获取新的配送清单信息
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @return
	 */
	public List<Record> getAssignNewList(String startDate,String endDate,Integer stationid,String status){
		List<Record> rList=findAssignGoodsList(startDate,endDate,stationid,status);
		List<Record> bList=getAssignBaseInfoList(startDate,endDate,stationid,status);
		List<Record> resList=dealAssignNewList(rList,bList);
		return resList;
	}
	
	/**
	 * 处理配送清单列表
	 * @param rList
	 * @param bList
	 * @return
	 */
	private List<Record> dealAssignNewList(List<Record> rList,List<Record> bList){
		List<Record> recList=new ArrayList<Record>();
		Record rec=null;
		if(rList!=null && rList.size()>0){
			Record r1=rList.get(0);
			BigDecimal cTotals=r1.getBigDecimal("SALE_NUMS");		// 客户商品件数汇总
			BigDecimal cwTotals=r1.getBigDecimal("SALE_WEIGHTS");
			BigDecimal sTotals=cTotals;					// 站点商品件数汇总
			BigDecimal swTotals=cwTotals;
			BigDecimal totals=cTotals;						// 全站商品件数汇总
			BigDecimal wtotals=cwTotals;
			rec=new Record();
			Record b1=findBaseRecordByCustId(bList,r1.getInt("cust_id"));
			rec.set("STATION_NAME", b1.getStr("STATION_NAME"));
			rec.set("STATION_MASTER", b1.getStr("STATION_MASTER"));
			rec.set("CUST_NAME", b1.getStr("CUST_NAME"));
			rec.set("MOBILE", b1.getStr("MOBILE"));
			rec.set("POSITION", b1.getStr("POSITION"));
			rec.set("GOODS_NAME", r1.getStr("GOODS_NAME")).set("SALE_NUMS",r1.getBigDecimal("SALE_NUMS")).set("SALE_WEIGHTS", r1.getBigDecimal("SALE_WEIGHTS"));
			recList.add(rec);
			b1=null;
			rec=null;
			Record b2=null;
			for(int i=1,size=rList.size();i<size;i++){
				Record r2=rList.get(i);				// 得到当前的记录
				totals=totals.add(r2.getBigDecimal("SALE_NUMS"));
				wtotals=wtotals.add(r2.getBigDecimal("SALE_WEIGHTS"));
				if(r1.getInt("station_id").intValue()!=r2.getInt("station_id").intValue() && r1.getInt("cust_id").intValue()!=r2.getInt("cust_id").intValue()){	// 站点不同，客户不同，进入的是新站统计信息
					b1=findBaseRecordByCustId(bList,r1.getInt("cust_id"));
					Record rec1=new Record();
					rec1.set("STATION_NAME", b1.getStr("STATION_NAME"));
					rec1.set("STATION_MASTER", b1.getStr("STATION_MASTER"));
					rec1.set("CUST_NAME", b1.getStr("CUST_NAME"));
					rec1.set("MOBILE", b1.getStr("MOBILE"));
//					rec1.set("POSITION", b1.getStr("POSITION"));
					rec1.set("POSITION", b1.getStr("POSITION")==null?"   ":b1.getStr("POSITION"));
					rec1.set("GOODS_NAME", "客户合计").set("SALE_NUMS", cTotals).set("SALE_WEIGHTS", cwTotals);
					recList.add(rec1);
					cTotals=r2.getBigDecimal("SALE_NUMS");
					cwTotals=r2.getBigDecimal("SALE_WEIGHTS");
					rec1=null;
					Record rec2=new Record();			
					rec2.set("STATION_NAME", b1.getStr("STATION_NAME"));
					rec2.set("STATION_MASTER", b1.getStr("STATION_MASTER"));
					rec2.set("CUST_NAME", "");
					rec2.set("MOBILE", "");
					rec2.set("POSITION", "");
					rec2.set("GOODS_NAME", "站点合计").set("SALE_NUMS", sTotals).set("SALE_WEIGHTS", swTotals);
					recList.add(rec2);
					rec2=null;
					sTotals=r2.getBigDecimal("SALE_NUMS");
					swTotals=r2.getBigDecimal("SALE_WEIGHTS");
				}else if(r1.getInt("cust_id").intValue()!=r2.getInt("cust_id").intValue()){
					b1=findBaseRecordByCustId(bList,r1.getInt("cust_id"));
					rec=new Record();
					rec.set("STATION_NAME", b1.getStr("STATION_NAME"));
					rec.set("STATION_MASTER", b1.getStr("STATION_MASTER"));
					rec.set("CUST_NAME", b1.getStr("CUST_NAME"));
					rec.set("MOBILE", b1.getStr("MOBILE"));
					rec.set("POSITION", b1.getStr("POSITION")==null?"   ":b1.getStr("POSITION"));
					rec.set("GOODS_NAME", "客户合计").set("SALE_NUMS", cTotals).set("SALE_WEIGHTS", cwTotals);
					recList.add(rec);
					rec=null;
					cTotals=r2.getBigDecimal("SALE_NUMS");
					cwTotals=r2.getBigDecimal("SALE_WEIGHTS");
					sTotals=sTotals.add(r2.getBigDecimal("SALE_NUMS"));
					swTotals=swTotals.add(r2.getBigDecimal("SALE_WEIGHTS"));					
				}else{				
					cTotals=cTotals.add(r2.getBigDecimal("SALE_NUMS"));
					cwTotals=cwTotals.add(r2.getBigDecimal("SALE_WEIGHTS"));
					sTotals=sTotals.add(r2.getBigDecimal("SALE_NUMS"));
					swTotals=swTotals.add(r2.getBigDecimal("SALE_WEIGHTS"));
				}
				b2=findBaseRecordByCustId(bList,r2.getInt("cust_id"));
				rec=new Record();
				rec.set("STATION_NAME", b2.getStr("STATION_NAME"));
				rec.set("STATION_MASTER", b2.getStr("STATION_MASTER"));
				rec.set("CUST_NAME", b2.getStr("CUST_NAME"));
				rec.set("MOBILE", b2.getStr("MOBILE"));
				rec.set("POSITION", b2.getStr("POSITION"));
				rec.set("GOODS_NAME", r2.getStr("GOODS_NAME")).set("SALE_NUMS",r2.getBigDecimal("SALE_NUMS")).set("SALE_WEIGHTS", r2.getBigDecimal("SALE_WEIGHTS"));
				recList.add(rec);
				rec=null;
				b1=null;
				b2=null;
				r1=rList.get(i);
				if(i==size-1){	// 最后一个
					b2=findBaseRecordByCustId(bList,r2.getInt("cust_id"));
					recList.add(new Record().set("STATION_NAME", b2.getStr("STATION_NAME")).set("STATION_MASTER", b2.getStr("STATION_MASTER")).set("CUST_NAME", b2.getStr("CUST_NAME")).set("MOBILE", b2.getStr("MOBILE")).set("POSITION", b2.getStr("POSITION")).set("GOODS_NAME", "客户合计").set("SALE_NUMS", cTotals).set("SALE_WEIGHTS", cwTotals));
					recList.add(new Record().set("STATION_NAME", b2.getStr("STATION_NAME")).set("STATION_MASTER", b2.getStr("STATION_MASTER")).set("GOODS_NAME", "站点合计").set("SALE_NUMS", sTotals).set("SALE_WEIGHTS", swTotals));
				}
			}
			recList.add(new Record().set("STATION_NAME", "  ").set("STATION_MASTER", "  ").set("CUST_NAME", "  ").set("MOBILE", "  ").set("POSITION", "  ").set("GOODS_NAME", "汇总统计").set("SALE_NUMS", totals).set("SALE_WEIGHTS", wtotals));		// 	增加全站汇总统计信息
		}
		return recList;
	}
	
	/**
	 * 查找每条商品对应的站点和客户基本信息
	 * @param rList
	 * @param cust_id
	 * @return
	 */
	private Record findBaseRecordByCustId(List<Record> rList,int cust_id){
		Record rec=null;
		if(rList!=null && rList.size()>0){
			for(Record r:rList){
				if(r.getInt("cust_id")==cust_id){
					rec=r;
					break;
				}
			}
		}
		return rec;
	}
	
	/**
	 * 查询符合条件的订单商品的汇总信息
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @param status
	 * @return
	 */
	private List<Record> findAssignGoodsList(String startDate,String endDate,Integer stationid,String status){
		List<Record> list=null;
		StringBuffer sql=new StringBuffer(" SELECT sta.STATION_ID,oi.CUST_ID,gi.GOODS_NAME,SUM(od.SALE_NUM) AS SALE_NUMS,SUM(gi.GOODS_SPEC*od.SALE_NUM) as SALE_WEIGHTS");
		sql.append(" FROM orderinfo oi INNER JOIN orderdetails od on oi.ORDER_ID=od.ORDER_ID");
		sql.append(" INNER JOIN customer cust on cust.CUST_ID=oi.CUST_ID");
		sql.append(" INNER JOIN station sta on sta.STATION_ID=cust.TAKE_STATION_ID");
		sql.append(" INNER JOIN goodsinfo gi on gi.GOODS_ID=od.GOODS_ID");
		sql.append(getAssignWhereSQL(startDate,endDate,stationid,status));
		sql.append(" GROUP BY sta.STATION_ID,oi.CUST_ID,gi.GOODS_NAME");
		sql.append(" order by sta.STATION_ID,oi.CUST_ID,gi.GOODS_ID");
		list=Db.find(sql.toString());
		return list;
	}
	
	/**
	 * 查询符合条件的订单客户的基本信息
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @param status
	 * @return
	 */
	private List<Record> getAssignBaseInfoList(String startDate,String endDate,Integer stationid,String status){
		List<Record> custList=null;
		StringBuffer sql=new StringBuffer(" SELECT sta.STATION_NAME,CONCAT(ui.USERNAME,ui.MOBILE) as STATION_MASTER,cust.CUST_NAME,cust.MOBILE,cust.POSITION,cust.CUST_ID");
		sql.append(" FROM orderinfo oi INNER JOIN customer cust on cust.CUST_ID=oi.CUST_ID");
		sql.append(" INNER JOIN station sta on sta.STATION_ID=cust.TAKE_STATION_ID");
		sql.append(" INNER JOIN userinfo ui ON ui.USERID=sta.STATION_MASTER");
		sql.append(getAssignWhereSQL(startDate,endDate,stationid,status));
		sql.append(" order by sta.STATION_NAME,cust.CUST_ID");
		custList=Db.find(sql.toString());
		return custList;
	}
	
	/**
	 * 获取查询条件的SQL语句
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @param status
	 * @return
	 */
	private String getAssignWhereSQL(String startDate,String endDate,Integer stationid,String status){
		StringBuffer condition=new StringBuffer();
		if(status.equals("0")){
			condition.append("  where oi.`STATUS`<>'over'");
		}else if(status.equals("-1")){
			condition.append(" where 1=1");
		}else{
			condition.append(" where oi.`STATUS`='"+status+"'");
		}
		if(startDate!=null && !startDate.equals("")){
			condition.append(" and TO_DAYS(oi.ORDER_DATE)>=TO_DAYS('"+startDate+"')");
		}
		if(endDate!=null && !endDate.equals("")){
			condition.append(" and TO_DAYS(oi.ORDER_DATE)<=TO_DAYS('"+endDate+"')");
		}
		if(stationid!=null && stationid!=-1){
			condition.append(" and sta.STATION_ID="+stationid);
		}
		return condition.toString();
	}
	
	/**
	 * 配送清单（站点统计）
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @return
	 */
	public List<Record> getAssignStationList(String startDate,String endDate,Integer stationid,String status){
		// 1 ：获取站点信息列表
		List<Record> staList=Db.find("select sta.STATION_ID,sta.STATION_NAME,sta.STATION_ADDRESS,ui.USERNAME,ui.MOBILE,sta.SEND_TIME from station sta INNER JOIN userinfo ui on ui.USERID=sta.STATION_MASTER");
		List<Record> recList=findAssignStationList(startDate,endDate,stationid,status);
		List<Record> list=dealAssignStationList(recList,staList);
		return list;
	}
	
	/**
	 * 查找站点信息
	 * @param staList
	 * @param stationid
	 * @return
	 */
	private String findStationById(List<Record> staList,int stationid){
		StringBuffer str=new StringBuffer("");
		if(staList!=null && staList.size()>0){
			for(Record rec:staList){
				if(rec.getInt("STATION_ID").intValue()==stationid){
					str.append(rec.getStr("STATION_NAME"));
					str.append("<br/>"+rec.getStr("USERNAME")+rec.getStr("MOBILE"));
					if(rec.getStr("SEND_TIME")!=null && rec.getStr("SEND_TIME").trim().length()!=0){
						str.append("<br/>"+rec.getStr("SEND_TIME"));
					}			
					break;
				}
			}
		}
		return str.toString();
	}
	
	/**
	 *  查询符合条件的配送清单（站点统计）
	 * @param startDate
	 * @param endDate
	 * @param stationid
	 * @param status
	 * @return
	 */
	private List<Record> findAssignStationList(String startDate,String endDate,Integer stationid,String status){
		List<Record> list=null;
		StringBuffer sb=new StringBuffer(" SELECT sta.STATION_ID, gi.GOODS_NAME,SUM(od.SALE_NUM) as SALE_NUMS");
		sb.append(" FROM orderinfo oi INNER JOIN orderdetails od on oi.ORDER_ID=od.ORDER_ID");
		sb.append(" INNER JOIN customer cust ON cust.CUST_ID=oi.CUST_ID");
		sb.append(" INNER JOIN station sta on sta.STATION_ID=cust.STATION_ID");
		sb.append(" INNER JOIN goodsinfo gi on gi.GOODS_ID=od.GOODS_ID");
		sb.append(getAssignWhereSQL(startDate,endDate,stationid,status));
		sb.append(" GROUP BY sta.STATION_ID, gi.GOODS_NAME");
		list=Db.find(sb.toString());
		return list;
	}
	
	/**
	 * 处理站点统计配送清单
	 * @param goodsList
	 * @param staList
	 * @return
	 */
	private List<Record> dealAssignStationList(List<Record> list,List<Record> staList){
		if(list==null || list.size()==0){
			return list;
		}
		List<Record> recList=new ArrayList<Record>();
		Record r1=list.get(0);
		Record r2=null;
		BigDecimal total=r1.getBigDecimal("SALE_NUMS");
		BigDecimal totals=total;
		String str=findStationById(staList,r1.getInt("STATION_ID"));
		recList.add(new Record().set("STATION", str).set("GOODS_NAME", r1.getStr("GOODS_NAME")).set("SALE_NUM", r1.getBigDecimal("SALE_NUMS")).set("DEMO", ""));
		for(int i=1,cnt=list.size();i<cnt;i++){
			r2=list.get(i);
			str=findStationById(staList,r2.getInt("STATION_ID"));
			totals=totals.add(r2.getBigDecimal("SALE_NUMS"));
			if(r1.getInt("STATION_ID").intValue()!=r2.getInt("STATION_ID").intValue()){
				recList.add(new Record().set("STATION", "").set("GOODS_NAME","站点合计").set("SALE_NUM",total).set("DEMO", ""));
				recList.add(new Record().set("STATION", str).set("GOODS_NAME", r2.getStr("GOODS_NAME")).set("SALE_NUM", r2.getBigDecimal("SALE_NUMS")).set("DEMO", ""));
				total=r2.getBigDecimal("SALE_NUMS");
			}else{
				total=total.add(r2.getBigDecimal("SALE_NUMS"));
				recList.add(new Record().set("STATION", str).set("GOODS_NAME", r2.getStr("GOODS_NAME")).set("SALE_NUM", r2.getBigDecimal("SALE_NUMS")).set("DEMO", ""));
			}
			r1=list.get(i);
		}
		recList.add(new Record().set("STATION", "").set("GOODS_NAME","站点合计").set("SALE_NUM",total).set("DEMO", ""));
		recList.add(new Record().set("STATION", "").set("GOODS_NAME","汇总统计").set("SALE_NUM",totals).set("DEMO", ""));
		return recList;
	}
}
